
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 05/15/2012 00:29:51
-- Generated from EDMX file: C:\Projects\imbatracker\ImbaTracker\ImbaTracker\Models\Imba.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ImbaDB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ProjectOwners_Project]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProjectOwners] DROP CONSTRAINT [FK_ProjectOwners_Project];
GO
IF OBJECT_ID(N'[dbo].[FK_ProjectOwners_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProjectOwners] DROP CONSTRAINT [FK_ProjectOwners_User];
GO
IF OBJECT_ID(N'[dbo].[FK_IssueDepedencies_Issue]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[IssueDepedencies] DROP CONSTRAINT [FK_IssueDepedencies_Issue];
GO
IF OBJECT_ID(N'[dbo].[FK_IssueDepedencies_Issue1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[IssueDepedencies] DROP CONSTRAINT [FK_IssueDepedencies_Issue1];
GO
IF OBJECT_ID(N'[dbo].[FK_IssueUserAssigner]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Issues] DROP CONSTRAINT [FK_IssueUserAssigner];
GO
IF OBJECT_ID(N'[dbo].[FK_IssueUserImplementer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Issues] DROP CONSTRAINT [FK_IssueUserImplementer];
GO
IF OBJECT_ID(N'[dbo].[FK_IssueProject]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Issues] DROP CONSTRAINT [FK_IssueProject];
GO
IF OBJECT_ID(N'[dbo].[FK_IssueIssueChildren]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Issues] DROP CONSTRAINT [FK_IssueIssueChildren];
GO
IF OBJECT_ID(N'[dbo].[FK_IssueStatus]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Issues] DROP CONSTRAINT [FK_IssueStatus];
GO
IF OBJECT_ID(N'[dbo].[FK_UserProject_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserProject] DROP CONSTRAINT [FK_UserProject_User];
GO
IF OBJECT_ID(N'[dbo].[FK_UserProject_Project]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserProject] DROP CONSTRAINT [FK_UserProject_Project];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Issues]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Issues];
GO
IF OBJECT_ID(N'[dbo].[Projects]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Projects];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO
IF OBJECT_ID(N'[dbo].[Status]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Status];
GO
IF OBJECT_ID(N'[dbo].[ProjectOwners]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProjectOwners];
GO
IF OBJECT_ID(N'[dbo].[IssueDepedencies]', 'U') IS NOT NULL
    DROP TABLE [dbo].[IssueDepedencies];
GO
IF OBJECT_ID(N'[dbo].[UserProject]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserProject];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Issues'
CREATE TABLE [dbo].[Issues] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Brief] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [StartDate] datetime  NULL,
    [PreferredEndDate] datetime  NULL,
    [CriticalEndDate] datetime  NULL,
    [Priority] int  NOT NULL,
    [AssignerId] int  NULL,
    [ImplementerId] int  NULL,
    [ProjectId] int  NOT NULL,
    [ParentId] int  NULL,
    [StatusId] int  NOT NULL
);
GO

-- Creating table 'Projects'
CREATE TABLE [dbo].[Projects] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] varchar(50)  NULL,
    [Hidden] bit  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Login] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Status'
CREATE TABLE [dbo].[Status] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ProjectOwners'
CREATE TABLE [dbo].[ProjectOwners] (
    [OwnedProjects_Id] int  NOT NULL,
    [Owners_Id] int  NOT NULL
);
GO

-- Creating table 'IssueDepedencies'
CREATE TABLE [dbo].[IssueDepedencies] (
    [Predecessor_Id] int  NOT NULL,
    [Successor_Id] int  NOT NULL
);
GO

-- Creating table 'UserProject'
CREATE TABLE [dbo].[UserProject] (
    [Members_Id] int  NOT NULL,
    [Projects_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Issues'
ALTER TABLE [dbo].[Issues]
ADD CONSTRAINT [PK_Issues]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Projects'
ALTER TABLE [dbo].[Projects]
ADD CONSTRAINT [PK_Projects]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Status'
ALTER TABLE [dbo].[Status]
ADD CONSTRAINT [PK_Status]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [OwnedProjects_Id], [Owners_Id] in table 'ProjectOwners'
ALTER TABLE [dbo].[ProjectOwners]
ADD CONSTRAINT [PK_ProjectOwners]
    PRIMARY KEY NONCLUSTERED ([OwnedProjects_Id], [Owners_Id] ASC);
GO

-- Creating primary key on [Predecessor_Id], [Successor_Id] in table 'IssueDepedencies'
ALTER TABLE [dbo].[IssueDepedencies]
ADD CONSTRAINT [PK_IssueDepedencies]
    PRIMARY KEY NONCLUSTERED ([Predecessor_Id], [Successor_Id] ASC);
GO

-- Creating primary key on [Members_Id], [Projects_Id] in table 'UserProject'
ALTER TABLE [dbo].[UserProject]
ADD CONSTRAINT [PK_UserProject]
    PRIMARY KEY NONCLUSTERED ([Members_Id], [Projects_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [OwnedProjects_Id] in table 'ProjectOwners'
ALTER TABLE [dbo].[ProjectOwners]
ADD CONSTRAINT [FK_ProjectOwners_Project]
    FOREIGN KEY ([OwnedProjects_Id])
    REFERENCES [dbo].[Projects]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Owners_Id] in table 'ProjectOwners'
ALTER TABLE [dbo].[ProjectOwners]
ADD CONSTRAINT [FK_ProjectOwners_User]
    FOREIGN KEY ([Owners_Id])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProjectOwners_User'
CREATE INDEX [IX_FK_ProjectOwners_User]
ON [dbo].[ProjectOwners]
    ([Owners_Id]);
GO

-- Creating foreign key on [Predecessor_Id] in table 'IssueDepedencies'
ALTER TABLE [dbo].[IssueDepedencies]
ADD CONSTRAINT [FK_IssueDepedencies_Issue]
    FOREIGN KEY ([Predecessor_Id])
    REFERENCES [dbo].[Issues]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Successor_Id] in table 'IssueDepedencies'
ALTER TABLE [dbo].[IssueDepedencies]
ADD CONSTRAINT [FK_IssueDepedencies_Issue1]
    FOREIGN KEY ([Successor_Id])
    REFERENCES [dbo].[Issues]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_IssueDepedencies_Issue1'
CREATE INDEX [IX_FK_IssueDepedencies_Issue1]
ON [dbo].[IssueDepedencies]
    ([Successor_Id]);
GO

-- Creating foreign key on [AssignerId] in table 'Issues'
ALTER TABLE [dbo].[Issues]
ADD CONSTRAINT [FK_IssueUserAssigner]
    FOREIGN KEY ([AssignerId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_IssueUserAssigner'
CREATE INDEX [IX_FK_IssueUserAssigner]
ON [dbo].[Issues]
    ([AssignerId]);
GO

-- Creating foreign key on [ImplementerId] in table 'Issues'
ALTER TABLE [dbo].[Issues]
ADD CONSTRAINT [FK_IssueUserImplementer]
    FOREIGN KEY ([ImplementerId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_IssueUserImplementer'
CREATE INDEX [IX_FK_IssueUserImplementer]
ON [dbo].[Issues]
    ([ImplementerId]);
GO

-- Creating foreign key on [ProjectId] in table 'Issues'
ALTER TABLE [dbo].[Issues]
ADD CONSTRAINT [FK_IssueProject]
    FOREIGN KEY ([ProjectId])
    REFERENCES [dbo].[Projects]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_IssueProject'
CREATE INDEX [IX_FK_IssueProject]
ON [dbo].[Issues]
    ([ProjectId]);
GO

-- Creating foreign key on [ParentId] in table 'Issues'
ALTER TABLE [dbo].[Issues]
ADD CONSTRAINT [FK_IssueIssueChildren]
    FOREIGN KEY ([ParentId])
    REFERENCES [dbo].[Issues]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_IssueIssueChildren'
CREATE INDEX [IX_FK_IssueIssueChildren]
ON [dbo].[Issues]
    ([ParentId]);
GO

-- Creating foreign key on [StatusId] in table 'Issues'
ALTER TABLE [dbo].[Issues]
ADD CONSTRAINT [FK_IssueStatus]
    FOREIGN KEY ([StatusId])
    REFERENCES [dbo].[Status]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_IssueStatus'
CREATE INDEX [IX_FK_IssueStatus]
ON [dbo].[Issues]
    ([StatusId]);
GO

-- Creating foreign key on [Members_Id] in table 'UserProject'
ALTER TABLE [dbo].[UserProject]
ADD CONSTRAINT [FK_UserProject_User]
    FOREIGN KEY ([Members_Id])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Projects_Id] in table 'UserProject'
ALTER TABLE [dbo].[UserProject]
ADD CONSTRAINT [FK_UserProject_Project]
    FOREIGN KEY ([Projects_Id])
    REFERENCES [dbo].[Projects]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserProject_Project'
CREATE INDEX [IX_FK_UserProject_Project]
ON [dbo].[UserProject]
    ([Projects_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------